import os
import django
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill

# 设置Django环境
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "house_crm.settings")
django.setup()

# 导入你需要生成模板的模型
# 请根据实际情况替换为你的应用和模型
from house.models import MeterReading


def generate_excel_template(model, file_path, include_fields=None, exclude_fields=None, add_examples=True):
    """
    根据Django模型生成Excel导入模板

    参数:
    - model: Django模型类
    - file_path: 生成的模板文件路径
    - include_fields: 要包含的字段列表，默认为None（包含所有字段）
    - exclude_fields: 要排除的字段列表，默认为None
    - add_examples: 是否添加示例数据行，默认为True
    """
    # 初始化Workbook
    wb = Workbook()
    ws = wb.active
    ws.title = f"{model.__name__}模板"
    
    # 获取模型字段
    all_fields = [field for field in model._meta.get_fields()
                  if hasattr(field, 'verbose_name') and hasattr(field, 'name')]
    
    # 筛选要包含的字段
    if include_fields:
        fields = [field for field in all_fields if field.name in include_fields]
    else:
        exclude_fields = exclude_fields or []
        fields = [field for field in all_fields if field.name not in exclude_fields]
    
    # 设置表头样式
    header_fill = PatternFill(start_color="DDDDDD", end_color="DDDDDD", fill_type="solid")
    
    # 写入表头行（第1行）
    for col, field in enumerate(fields, 1):
        cell = ws.cell(row=1, column=col)
        # 表头使用verbose_name，如果没有则使用字段名
        cell.value = str(field.verbose_name) if field.verbose_name else field.name
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
        cell.fill = header_fill
    
    # 写入字段名行（第2行，供参考）
    for col, field in enumerate(fields, 1):
        cell = ws.cell(row=2, column=col)
        cell.value = f"[字段名: {field.name}]"
        cell.font = Font(size=9, color="666666")
    
    # 写入字段类型行（第3行，供参考）
    for col, field in enumerate(fields, 1):
        cell = ws.cell(row=3, column=col)
        field_type = str(field.__class__.__name__).replace('Field', '')
        cell.value = f"[类型: {field_type}]"
        cell.font = Font(size=9, color="666666")
    
    # 添加示例数据行（第4行）
    if add_examples:
        for col, field in enumerate(fields, 1):
            cell = ws.cell(row=4, column=col)
            field_type = field.__class__.__name__
            
            # 根据字段类型提供不同的示例
            if 'CharField' in field_type:
                cell.value = "示例文本"
            elif 'IntegerField' in field_type:
                cell.value = 123
            elif 'FloatField' in field_type:
                cell.value = 123.45
            elif 'BooleanField' in field_type:
                cell.value = "是/否"
            elif 'DateField' in field_type:
                cell.value = "2023-12-31"
            elif 'DateTimeField' in field_type:
                cell.value = "2023-12-31 23:59:59"
            elif 'ForeignKey' in field_type:
                cell.value = f"请输入{field.related_model.__name__}的ID"
            else:
                cell.value = "示例值"
            
            cell.font = Font(color="999999")
    
    # 调整列宽
    for col in range(1, len(fields) + 1):
        ws.column_dimensions[chr(64 + col)].width = 25
    
    # 添加说明
    ws.cell(row=6, column=1).value = "注意:"
    ws.cell(row=7, column=1).value = "1. 请从第5行开始填写实际数据（如果添加了示例）"
    ws.cell(row=8, column=1).value = "2. 日期格式请保持一致"
    ws.cell(row=9, column=1).value = "3. 外键字段请填写关联对象的ID"
    
    # 保存文件
    wb.save(file_path)
    print(f"成功生成 {model.__name__} 导入模板到 {file_path}，共 {len(fields)} 个字段")


if __name__ == "__main__":
    # 示例用法
    # 替换为你的模型和导出路径
    template_path = os.path.join(os.path.dirname(__file__), "数据导入模板.xlsx")
    
    # 生成模板，可以指定要包含或排除的字段
    generate_excel_template(
        model=MeterReading,
        file_path=template_path,
        # include_fields=['name', 'email', 'age'],  # 只包含这些字段
        exclude_fields=['id', 'created_at', 'updated_at'],  # 排除这些字段
        add_examples=True  # 添加示例数据
    )
